package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.xmy.cultivate.entity.Grade;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.Staff;
import com.xmy.cultivate.entity.Student;
import com.xmy.cultivate.entity.response.MathChaPlan;
import com.xmy.cultivate.entity.response.YearAndQuarter;
import com.xmy.cultivate.entity.views.LeadDataRs;
import com.xmy.cultivate.respons.ResSchool;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 班级 Mapper 接口
 * </p>
 *
 * @author min
 * @since 2022-07-26
 */
@Mapper
public interface GradeMapper extends BaseMapper<Grade> {

    @Select("SELECT * FROM grade")
    @Results(
            {@Result(
                    column = "id",property = "id"
            ),
            @Result(
                    property = "coursePricingList"
                    ,column = "id",
                    one = @One(select = "com.xmy.cultivate.mapper")
            )
    })
    public abstract List<Map<String,Object>> getGradeList(Map<String,Object> map);

    @Select("select a.* from grade as a left join course as b on a.course_id = b.id ${ew.customSqlSegment}")
    @Results({
            @Result( column = "id",property = "id"),
            @Result(
                    property = "courseObj"
                    ,column = "course_id",
                    one=@One(select = "com.xmy.cultivate.mapper.CourseMapper.checkCourseForId")
            ),
            @Result(property = "teacherObj"
                    ,column = "teacher_id",
                    one=@One(select = "com.xmy.cultivate.mapper.StaffMapper.selectById")
            )
    })
    public abstract IPage<Grade> checkGrade(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);


    @Select("select a.id as studentGradeId,b.id as gradeId,b.`name` as gradeName,b.course_id,getCourseNameForId(b.course_id),b.school_id,getSchoolName(b.school_id) as schoolName from student_grade as a left join grade as b on  a.student_id = b.id where a.status =1 and a.student_id= #{studentId}")
    public abstract List<Map<String,Object>> GetLessonByStuInfoId(Long studentId);


    @Select("SELECT * from grade_view ${ew.customSqlSegment}")
    @Results({
            @Result( column = "id",property = "id"),
            @Result(
                    property = "courseObj"
                    ,column = "course_id",
                    one=@One(select = "com.xmy.cultivate.mapper.CourseMapper.checkCourseForId")
            ),
            @Result(property = "teacherObj"
                    ,column = "teacher_id",
                    one=@One(select = "com.xmy.cultivate.mapper.StaffMapper.selectById")
            ),
            @Result(property = "schoolObj"
                    ,column = "school_id",
                    one=@One(select = "com.xmy.cultivate.mapper.OrganizationMapper.selectById")
            )
    })
    public abstract IPage<Grade> findAll(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);


    @Select("select a.* from grade as a where id = #{id}")
    @Results({
            @Result( column = "id",property = "id"),
            @Result(
                    property = "courseObj"
                    ,column = "course_id",
                    one=@One(select = "com.xmy.cultivate.mapper.CourseMapper.checkCourseForId")
            ),
            @Result(property = "teacherObj"
                    ,column = "teacher_id",
                    one=@One(select = "com.xmy.cultivate.mapper.StaffMapper.selectById")
            ),
            @Result(property = "schoolObj"
                    ,column = "school_id",
                    one=@One(select = "com.xmy.cultivate.mapper.OrganizationMapper.selectById")
            ),
            @Result(
                    property = "teacherList"
                    ,column = "id",
                    many=@Many(select = "com.xmy.cultivate.mapper.TypeValueDataMapper.selectListByForId")
            )
    })
    public abstract Grade getOneForId(@Param("id") Long id);

    @Select({
            "<script>",
            "SELECT g.name,g.full_count,st.name as teacherName,COUNT(*) AS studentCount ",
            "FROM grade g",
            "INNER JOIN student_grade sg ON g.id = sg.grade_id ",
            "INNer JOIn staff st ON g.teacher_id = st.id",
            " ${ew.customSqlSegment}",
            "</script>"
    })
    public abstract IPage<Grade> selectGrade(IPage<?> page,@Param(Constants.WRAPPER) Wrapper wrapper);


    @Select({
            "<script>",
            "SELECT COUNT(DISTINCT sg.student_id) AS residueNumber FROM student_grade sg LEFT JOIN grade g on sg.grade_id = g.id WHERE (sg.status = 1 AND sg.created_at = (SELECT MAX(sg2.created_at)",
            "  FROM student_grade sg2",
            "  WHERE sg.student_id = sg2.student_id",
            "    AND sg.school_id = sg2.school_id)) and sg.grade_id = #{grade_id} and sg.subjects_id = #{subjects_id} and sg.quarter_num = #{quarter_num}",
            "</script>"
    })
    public abstract Integer getgradeCount(@Param("grade_id") Long gradeId,@Param("subjects_id") String subjects_id,@Param("quarter_num") String quarter);

    /**
     * 获取教师的带班科目id
     * @param yearPart
     * @param quarterNum
     * @param teacherId
     * @return
     */
    @Select("select subjects_id from grade as a LEFT JOIN course as b on a.course_id = b.id where a.year_part = ${yearPart} and b.quarter_num=${quarterNum} and a.teacher_id = ${teacherId} and a.lesson_type=${lessonType} GROUP BY b.subjects_id")
    public List<Long> getTeacherGradeSubjects(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("teacherId") Long teacherId,@Param("lessonType") Integer lessonType);

    /**
     * 获取所报读科目的剩余课时
     * @param schoolId
     * @param studentId
     * @param subjectsId
     * @return
     */
    @Select({
            "<script>",
            "select ifnull(sum(residue_course_count),0) as residue_course_count from order_detail where deleted=0 and status &lt;&gt; 2 ",
            "<when test='schoolId!=null'>",
            "AND school_id = ${schoolId}",
            "</when>",
            "and student_id=#{studentId} and subjects_id=#{subjectsId} and residue_course_count>0 and year_and_quarter>=${yearAndQuarter} and lesson_type=${lessonType}",
            "</script>"
    })
    public abstract Integer getResidueCourseCountForSubjects(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("yearAndQuarter") Integer yearAndQuarter,@Param("lessonType") Integer lessonType);


    @Select({"<script>",
            "SELECT",
                    "t2.id,",
                    "t2.`name`,",
                    "t1.school_id ",
                    "FROM",
                    "grade AS t1",
                    "INNER JOIN course as t3 on t1.course_id = t3.id",
                    "INNER JOIN staff AS t2 ON t1.teacher_id = t2.id ",
                    "WHERE",
                    "t1.deleted = 0 ",
                    "and t1.year_part = ${yearPart} ",
                    "and t1.quarter_num = ${quarterNum} ",
                    "<when test='yearClassId!=null and yearClassId!= &apos;&apos;'>",
                    "AND t1.year_class_id in(${yearClassId}) ",
                    "</when>",
                    "<when test='classTypeId!=null and classTypeId!= &apos;&apos;'>",
                    "AND t1.course_type_id in(${classTypeId}) ",
                    "</when>",
                    "<when test='subjectsId!=null and subjectsId!= &apos;&apos;'>",
                    "AND t3.subjects_id in(${subjectsId}) ",
                    "</when>",
                    "<when test='schoolId!=null and schoolId!= &apos;&apos;'>",
                    "AND t1.school_id in(${schoolId}) ",
                    "</when>",
                    "<when test='name!=null and name!= &apos;&apos;'>",
                    "AND t2.name like '%${name}%'",
                    "</when>",
                    "GROUP BY",
                    "t1.teacher_id",
            "</script>"})
    public abstract IPage<Staff> getGradeTeacher(IPage<?> page,
                                                 @Param("yearPart") Integer yearPart,
                                                 @Param("quarterNum") Integer quarterNum,
                                                 @Param("schoolId") String schoolId,
                                                 @Param("subjectsId") String subjectsId,
                                                 @Param("yearClassId") String yearClassId,
                                                 @Param("classTypeId") String classTypeId,
                                                 @Param("name") String name
                                                 );

    @Select({"<script>",
            "SELECT *,",
            "MAX( CASE WHEN subjects_id = 1 THEN school_time END ) AS en_school_time,",
            "MAX( CASE WHEN subjects_id = 2 THEN school_time END ) AS ma_school_time,",
            "MAX( CASE WHEN subjects_id = 3 THEN school_time END ) AS ph_school_time ",
            "FROM (",
                "SELECT *, ",
                "GROUP_CONCAT( time_start_end ORDER BY t.week_dates ) AS school_time ",
                "FROM (",
                    "SELECT t1.id,t1.year_part,t1.quarter_num,t1.student_id,t1.subjects_id,t2.week_dates,",
                    "CONCAT( xmy_erp_db.getWeekdayName ( t2.week_dates ), '', t2.time_start, ' - ', t2.time_end ) AS time_start_end ",
                    "FROM ",
                    "xmy_erp_db.student_grade AS t1 ",
                    "INNER JOIN xmy_erp_db.grade AS t2 ON t1.grade_id = t2.id ",
                    "WHERE ",
                    "t1.year_part = ${yearPart} ",
                    "AND t1.quarter_num = ${quarterNum} ",
                    "AND t1.lesson_type &lt;&gt; 1",
                    "AND t1.deleted = 0 ",
                    "AND t1.`status` = 1 ",
                    "AND t1.student_id IN( ${studentIdStr} ) ",
                    "<if test= 'readingType == 1'>",
                        "<if test= 'isBeforQuarter == 0'>",
                            "AND t1.reading_status = 1 ",
                        "</if>",
                        "<if test= 'isBeforQuarter == 1'>",
                            "AND t1.reading_status IN ( 1,3,4,5,6 ) ",
                        "</if>",
                    "</if>",
                    "<if test= 'readingType == 0'>",
                        "AND t1.reading_status IN ( 1,3,4,5,6 ) ",
                    "</if>",
                    "GROUP BY t1.grade_id,t1.student_id,t1.subjects_id ",
                ") AS t ",
                "GROUP BY student_id,subjects_id ",
            ") AS t ",
            "GROUP BY student_id",
            "</script>"})
    public abstract List<MathChaPlan> getSchoolTimeList(
            @Param("yearPart") Integer yearPart,
            @Param("quarterNum") Integer quarterNum,
            @Param("readingType") String readingType,
            @Param("isBeforQuarter") String isBeforQuarter,
            @Param("studentIdStr") String studentIdStr
    );


    @Select("select GROUP_CONCAT(DISTINCT year_class_id ORDER BY year_class_id asc) from grade as t1 where t1.year_part = ${yearPart} and t1.quarter_num = ${quarterNum} and t1.deleted = 0 and t1.lesson_type = 1 and t1.teacher_id = ${teacherId}")
    public String getYearClassIdForTeacherId(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("teacherId") Long teacherId);

}
